package com.edu.oracle.dao;

import com.edu.oracle.pojo.Student;
import com.edu.oracle.pojo.ViewStudent;
import com.edu.oracle.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {//student表

    public List<Student> getStudentList() {
        /**
         * 1.加载驱动
         * 2.获取连接对象
         * 3.创建sql语句操作对象:pre
         * 4.处理结果集ResultSet
         * 5.关闭资源
         */
        Connection connection=null;
        PreparedStatement pstm=null;
        ResultSet rs=null;

        try {
//            //1.加载驱动  alt +回车enter
//            Class.forName("oracle.jdbc.driver.OracleDriver");
//
//            //2.
//            String url ="jdbc:oracle:thin:@//localhost:1521/orcl";
//              //jdbc:oracle:thin:@//localhost:1521/orcl
//            connection = DriverManager.getConnection(url,"scott","oracle");

            connection = JdbcUtils.getConnection();

            //3.
            //3.1书写sql语句
            pstm =connection.prepareStatement("select * from student");
            //3.2执行SQL语句
           rs= pstm.executeQuery();//Query查找的意思， update更改的意思

            //4.处理结果集
            List<Student> studentList = new ArrayList<>();///每次存储一个对象单元
            while(rs.next()){
                Student student=new Student();///
                // 数据库中一行就是一个对象   最大存储一个对象，可以只存储一行数据，一个字段
                student.setSno(rs.getString("sno"));//学号
                student.setSname(rs.getString("sname"));//姓名
                student.setSbirthday(rs.getDate("sbirthday"));//出生日期
                student.setSclass(rs.getString("sclass"));//sclass字段名称
                student.setSpeciality(rs.getString("speciality"));//专业
                student.setTc(rs.getInt("tc"));//总分
                student.setSsex(rs.getString("ssex"));//性别

                studentList.add(student);
                //return student;//一个对象的时候  几个对象？？？
            }
            return studentList;///返回list集合


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.反推关闭
            JdbcUtils.closeResoure(connection,pstm,rs);
//            try {
//                rs.close();
//                pstm.close();
//                connection.close();
//
//
//            } catch (SQLException throwables) {
//                throwables.printStackTrace();
//            }
        }


        return  null;
    }

    public Student selectByOne(String sno) {
        /**
         * 1.加载驱动
         * 2.获取连接对象
         * 3.创建sql语句操作对象:pre
         * 4.处理结果集ResultSet
         * 5.关闭资源
         */

        Connection connection=null;
        PreparedStatement pstm=null;
        ResultSet rs=null;
        try {
            //1.加载驱动  alt +回车enter
            Class.forName("oracle.jdbc.driver.OracleDriver");

            //2.
            String url ="jdbc:oracle:thin:@//localhost:1521/orcl";
            //jdbc:oracle:thin:@//localhost:1521/orcl
            connection = DriverManager.getConnection(url,"scott","oracle");

            //3.
            //3.1书写sql语句
            pstm =connection.prepareStatement("select * from student where sno=?");
            //3.2执行SQL语句
            pstm.setString(1,sno);
            rs= pstm.executeQuery();//Query查找的意思， update更改的意思

            //4.处理结果集
            Student student=new Student();///List集合去掉
            while(rs.next()){

                // 数据库中一行就是一个对象   最大存储一个对象，可以只存储一行数据，一个字段
                student.setSno(rs.getString("sno"));//学号
                student.setSname(rs.getString("sname"));//姓名
                student.setSbirthday(rs.getDate("sbirthday"));//出生日期
                student.setSclass(rs.getString("sclass"));//sclass字段名称
                student.setSpeciality(rs.getString("speciality"));//专业
                student.setTc(rs.getInt("tc"));//总分
                student.setSsex(rs.getString("ssex"));//性别
                //list去掉
            }
            return student;///返回list集合 》》返回一个student对象


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //5.反推关闭
            try {
                rs.close();
                pstm.close();
                connection.close();


            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return null;
    }

    public List<ViewStudent> viewSelect() {//只要不是void 类型，都要返回数据

        /**
         * 1.加载驱动
         * 2.获取连接对象
         * 3.创建sql语句操作对象:pre
         * 4.处理结果集ResultSet
         * 5.关闭资源
         */

        Connection connection=null;
        PreparedStatement pstm=null;
        ResultSet rs=null;
        try {
            //1.加载驱动  alt +回车enter
            Class.forName("oracle.jdbc.driver.OracleDriver");

            //2.
            String url ="jdbc:oracle:thin:@//localhost:1521/orcl";
            //jdbc:oracle:thin:@//localhost:1521/orcl
            connection = DriverManager.getConnection(url,"scott","oracle");//soctt >> system

            //3.
            //3.1书写sql语句
            pstm =connection.prepareStatement("select * from vwStuCScore");
            //3.2执行SQL语句
            rs= pstm.executeQuery();//Query查找的意思， update更改的意思

            //4.处理结果集
            List<ViewStudent> studentList = new ArrayList<>();///每次存储一个对象单元
            while(rs.next()){
                ViewStudent student=new ViewStudent();///
                // 数据库中一行就是一个对象   最大存储一个对象，可以只存储一行数据，一个字段
                student.setSno(rs.getString("sno"));//学号
                student.setSname(rs.getString("sname"));//姓名
                student.setSsex(rs.getString("ssex"));//性别
                student.setGrade(rs.getInt("grade"));
                student.setCname(rs.getString("cname"));

                studentList.add(student);
                //return student;//一个对象的时候  几个对象？？？
            }
            return studentList;///返回list集合


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //5.反推关闭
            try {
                rs.close();
                pstm.close();
                connection.close();


            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return null;
    }

    public boolean updateStudent(Student student) {
        //jdbc

        /**
         * 1.加载驱动
         * 2.获取连接对象
         * 3.创建sql语句操作对象:pre
         * 4.处理结果集ResultSet
         * 5.关闭资源
         */

        Connection connection=null;
        PreparedStatement pstm=null;
        ResultSet rs=null;
        try {
            //1.加载驱动  alt +回车enter
            Class.forName("oracle.jdbc.driver.OracleDriver");

            //2.
            String url ="jdbc:oracle:thin:@//localhost:1521/orcl";
            //jdbc:oracle:thin:@//localhost:1521/orcl
            connection = DriverManager.getConnection(url,"scott","oracle");//soctt >> system
            connection.setAutoCommit(false);//oralce默认事务是不提交数据的，最终修改后，重启数据库，数据不变

            //3.1书写sql语句
            String sql="update student set sname=?,ssex=?,sclass=?,speciality=?,tc=?,sbirthday=? where sno=?";
            pstm =connection.prepareStatement(sql);

            pstm.setString(1,student.getSname());
            pstm.setString(2,student.getSsex());
            pstm.setString(3,student.getSclass());
            pstm.setString(4,student.getSpeciality());
            pstm.setInt(5,student.getTc());
            pstm.setDate(6,student.getSbirthday());
            pstm.setString(7,student.getSno());

            //3.2执行SQL语句
            int up = pstm.executeUpdate();//Query查找的意思， update更改的意思

            connection.commit();//提交事务
            //4.处理结果集
            if(up!=0) {
                return true;
            }else {
                return false;
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //5.反推关闭
            try {
                if(rs!=null){
                    rs.close();
                }

                pstm.close();
                connection.close();


            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }

    public Boolean addByOne(Student student){
        //jdbc
        //1.加载驱动
        //2.创建连接对象
        Connection conn=null;//全局变量
        PreparedStatement pstm =null;
        //3.创建sql操作对象
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            String url="jdbc:oracle:thin:@//localhost:1521/orcl";
            //jdbc:oracle:thin:@//localhost:1521/orcl
            String user="scott";
            String pass="oracle";
            conn=DriverManager.getConnection(url,user,pass);

            String sql="insert into student values (?,?,?,?,?,?,?)";//七个问号
            pstm=conn.prepareStatement(sql);
            //3.1拼接sql语句完成后
            pstm.setString(1,student.getSno());//1.代表拼接第一个问号
            pstm.setString(2,student.getSname());//2.代表拼接第2个问号
            pstm.setString(3,student.getSsex());//3.代表拼接第3个问号？
            pstm.setDate(4,student.getSbirthday());
            pstm.setString(5,student.getSpeciality());
            pstm.setString(6,student.getSclass());
            pstm.setInt(7,student.getTc());

            //3.2执行语句 第一种 查询，第二种更新：修改数据和插入数据
            int i = pstm.executeUpdate();
            //4.处理结果集
            if(i!=0){
                return true;
            }else {
                return false;
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {  //5.关闭资源
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return false;
    }

    public void deleteStudent(String sno) {
        /**
         * jdbc
         * 1.加载驱动
         * 2.创建连接对象
         *
         * 3.创建sql操作对象
         * 4.处理结果集
         *
         * 5.关闭资源
         */


        //1.加载驱动
        //2.创建连接对象
        Connection conn=null;//全局变量
        PreparedStatement pstm =null;
        //3.创建sql操作对象
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            String url="jdbc:oracle:thin:@//localhost:1521/orcl";
            //jdbc:oracle:thin:@//localhost:1521/orcl
            String user="scott";
            String pass="oracle";
            conn=DriverManager.getConnection(url,user,pass);

            conn.setAutoCommit(false);//不提交事务，mysql是默认提交的conn.setAutoCommit(true);

            //3.操作对象
            String sql="delete from student where sno=?";//七个问号
            pstm=conn.prepareStatement(sql);
            //3.1拼接sql语句完成后
            pstm.setString(1,sno);//1.代表拼接第一个问号

            //3.2执行语句 第一种 查询，第二种更新：修改数据、插入数据和删除
            int i = pstm.executeUpdate();


            conn.commit();//提交事务


            //4.处理结果集

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {  //5.关闭资源
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
}
